package com.shuqi.common;

import android.os.Environment;

import com.shuqi.model.database.PageCacheHelper;

/**
 * 建表语句
 * 
 * @author zp
 * 
 */
public class SqlCase {
    /** 书旗客户端书签数据库路径(不含'/'字符) */
    public static final String OLD_DATA_PATH = Environment.getDataDirectory()
            + "/data/com/shuqi/controller/databases";
    /**
     * 表名
     */
    public static final String BOOK_SHUQIAN_TABLE = "book_mark";
    /**
     * 书签表
     */
    public static final String TABLE_BOOK_SHUQIAN = "create table if not exists book_mark(" //
            + "id integer primary key," + "mark_title varchar," // 书签标题(书名)
            + "percent varchar," // 百分比
            + "mark_content varchar," // 书签介绍
            + "book_id varchar," // 书籍id
            + "source_id varchar," // 来源ID
            + "cid varchar," // 章节ID
            + "chapter_filename varchar," // 章节名
            + "chapter_pay_mode varchar," // 章节付费模式 0,免费章节,1全本,2按章,3全本免费章节
            + "isload varchar," // 1表示正常书签,-1表示正在下载
            + "new_chapter varchar," // 1表示正常书签,-1表示正在下载是否有更新,1有更新
            + "type varchar," // 书类型
            + "flag varchar," // 标识位
            + "img_url varchar," // 图片地址
            + "update_time int64," // 创建时间
            + "filename varchar," // 文件路径
            + "book_byte long," // 读到的字节数
            + "book_bytenum long," // 总字节数
            + "param1 varchar," // 旧数据库读到的字数
            + "param2 varchar," // 旧数据库总字数
            + "isupdated varchar," // 是否有更新,1有更新
            + "numchapter int," // 总章节数
            + "account varchar)";// 用户id

    /**
     * 书籍信息表
     */
    public static final String TABLE_BOOK_INFO = "create table if not exists bookinfos(" //
            + "book_id varchar," // 书籍id
            + "source_id varchar," // 来源ID
            + "img_url varchar," // 封面图
            + "book_name varchar," // 书名
            + "author_name varchar," // 作者名
            + "book_type varchar," // 书籍类型
            + "oid_max integer default 0," // 最大目录序列号
            + "pay_mode varchar," // 付费类型,0免费,1全本,2按章,3全本免费
            + "price varchar," // 价格,千字单价或者全本价格
            + "paid varchar,"// 0表示未支付,1表示支付
            + "down_count integer,"// 已下载章节数量
            + "info_update_time int64,"// 书籍信息最后更新时间
            + "chapter_update_time int64,"// 书籍章节列表最后更新时间
            + "word_count varchar," // 字数
            + "status varchar," // 状态,1连载,2完结
            + "source_type varchar," // 来源类型， 1书旗，2神马
            + "intro varchar," // 书籍简介
            + "hide varchar," // 总状态位Y-该书隐藏，N-该书开放
            + "cr_cover_isopen varchar," // 收费客户端封面页状态位1-开放，0-隐藏
            + "cr_read_isopen varchar," // 收费客户端阅读页页状态位1-开放，0-隐藏
            + "in_history varchar default '1'," // 1,表示是在书籍浏览历史中,0表示不在
            + "account varchar default '8000000')";// 账户
    /**
     * 页面缓存表
     */
    public static final String TABLE_BOOK_PAGE_CACHE =
            "create table if not exists book_page_cache(" + "id integer primary key,"
                    + "book_id varchar," // 书籍id
                    + "page varchar,"// 页面
                    + "page_type varchar,"// 页面的子页面类型
                    + "item_type varchar,"// 缓存详细类型
                    + "source_id varchar," // 来源ID
                    + "img_url varchar," // 封面图
                    + "book_name varchar," // 书名
                    + "author_name varchar," // 作者名
                    + "book_type varchar," // 书籍类型
                    + "book_updatecharpter varchar,"// 最新更新章节
                    + "oid_max integer default 0," // 最大目录序列号
                    + "pay_mode varchar," // 付费类型,0免费,1全本,2按章,3全本免费
                    + "price varchar," // 价格,千字单价或者全本价格
                    + "paid varchar,"// 0表示未支付,1表示支付
                    + "down_count integer,"// 已下载章节数量
                    + "info_update_time int64,"// 书籍信息最后更新时间
                    + "chapter_update_time int64,"// 书籍章节列表最后更新时间
                    + "word_count varchar," // 字数
                    + "status varchar," // 状态,1连载,2完结
                    + "intro varchar," // 书籍简介
                    + "discount varchar," // 是否有优惠;0没有 1有
                    + "hide varchar," // 总状态位Y-该书隐藏，N-该书开放
                    + "cr_cover_isopen varchar," // 收费客户端封面页状态位1-开放，0-隐藏
                    + "in_history varchar default '1'," // 1,表示是在书籍浏览历史中,0表示不在
                    + "account varchar default '8000000')";// 账户
    /**
     * 书籍目录表
     */
    public static final String TABLE_BOOK_CATALOG = "create table if not exists chapters(" //
            + "id integer primary key," + "oid integer," // 目录序列号
            + "cid varchar," // 章节id
            + "name varchar," // 章节名
            + "vid varchar," // 所属卷id
            + "is_chapter varchar," // 1,表示是章节,0表示是卷
            + "is_down varchar default '0'," // 1,表示是已下载,0表示是未下载
            + "pay_mode varchar," // 章节付费类型,0免费,1全本,2按章
            + "price varchar," // 价格
            + "paid varchar,"// 0表示未支付,1表示支付
            + "content_url varchar," // 对应章节CDN地址
            + "source_link varchar," // 来源地址
            + "word_count integer)";// 字数
    /**
     * 用户信息表
     */
    public static final String TABLE_USERINFO = "create table if not exists userinfos(" //
            + "id integer primary key," + "userid  varchar," // 用户ID
            + "password  varchar," // 密码
            + "session  varchar," // 用户session
            + "nikename  varchar," // 昵称
            + "gender  varchar," // 性别
            + "balance  varchar default '0'," // 余额
            + "mobile  varchar," // 手机号
            + "mobileshow   varchar," // 手机号
            + "email   varchar," // 邮件
            + "emailshow   varchar," // 可显示的邮箱名
            + "sina    varchar," // 新浪微博key
            + "sinaname    varchar," // 新浪微博用户名
            + "userstate  varchardefault '0'," // 当前验证状态（0未验证，1验证通过，2，验证失败）
            + "loginstate  varchardefault '0',"// 使用状态（0未使用，1当前正在使用）
            + "isencry  int," + "logintime  int64)";// 登录时间13位
    /**
     * txt书籍下载
     */
    public static final String TABLE_TXT_LOAD = "create table if not exists load_txts(" //
            + "id integer primary key," //
            + "user_id varchar," // 书籍id
            + "book_id varchar," // 书籍id
            + "book_name varchar," // 书籍名称
            + "file_name varchar," // 文件名称
            + "author varchar," // 作者
            + "url varchar," // 下载地址
            + "file_path varchar," // 存储路径
            + "file_size varchar," // 文件大小
            + "downloaded_size varchar," // 下载的大小
            + "create_time int64," // 创建更新时间
            + "isdownloaded varchar)";// 下载状态 1下载完成；2下载失败
    /**
     * 精准统计
     */
    public static final String TABLE_BOOK_PV = "create table if not exists books_pv(" //
            + "id integer primary key," //
            + "user_id varchar," // 用户id
            + "book_id varchar," // 书籍id
            + "chapter_id varchar," // 章节id
            + "create_time int64)"; // 创建更新时间


    /**
     * txt智能断章目录表
     */
    public static final String TABLE_TXT_CATALOG = "create table if not exists txt_catalog(" //
            + "id integer primary key," //
            + "path varchar," // 文件路径
            + "chapterindex varchar," // 章节索引
            + "chaptername varchar)"; // 章节名字
    /**
     * 触发器 添加点击书籍记录时删除超限的旧记录 每超过200条删除100条 version 5开始
     */
    public static final String TRIGGER_SEARCH_CACHE_DELETE =
            "Create Trigger if not exists search_cache_delete before insert on [book_page_cache] FOR EACH ROW"
                    + " begin "
                    + " delete from book_page_cache where (select count(id) from book_page_cache where item_type = "
                    + PageCacheHelper.BOOKSEARCH_CHILD_CUR_SEAECHWORD + ") >=100 "
                    + " and id in (select id from book_page_cache where cachetype = "
                    + PageCacheHelper.BOOKSEARCH_CHILD_CUR_SEAECHWORD
                    + " order by id asc limit 50); " + "end; ";
}
